from typing import List
from app.dao.models.mysql_gen import StudentScore
from .base_dao import getDatabaseSession
from sqlalchemy import func


class StudentScoreDAO:

    @classmethod
    def add(cls, score: StudentScore):
        with getDatabaseSession() as session:
            session.add(score)
            session.commit()

    @classmethod
    def get_scores_by_student_id(cls, student_id: str) -> List[StudentScore]:
        with getDatabaseSession() as session:
            return (
                session.query(StudentScore)
                .filter(StudentScore.student_id == student_id)
                .order_by(
                    StudentScore.academic_year,
                    StudentScore.semester,
                    StudentScore.course_name,
                )
                .all()
            )

    @classmethod
    def count_students_by_major(cls) -> list[dict]:
        """
        按专业统计学生数量（去重 student_id）
        """
        with getDatabaseSession() as session:
            result = (
                session.query(
                    StudentScore.major,
                    func.count(func.distinct(StudentScore.student_id)),
                )
                .group_by(StudentScore.major)
                .all()
            )
            return [{"major": row[0], "student_count": row[1]} for row in result]

    @classmethod
    def add_batch(cls, scores: list[StudentScore]):
        with getDatabaseSession() as session:
            session.bulk_save_objects(scores)
            session.commit()

    @classmethod
    def get_by_student_id(cls, student_id: str):
        with getDatabaseSession() as session:
            return session.query(StudentScore).filter_by(student_id=student_id).all()

    @classmethod
    def delete_by_id(cls, id: str):
        with getDatabaseSession() as session:
            session.query(StudentScore).filter_by(id=id).delete()
            session.commit()

    @classmethod
    def update_score(cls, id: str, new_score: float):
        with getDatabaseSession() as session:
            record = session.query(StudentScore).filter_by(id=id).first()
            if record:
                record.score = new_score
                session.commit()

    @classmethod
    def delete_all(cls):
        with getDatabaseSession() as session:
            session.query(StudentScore).delete()
            session.commit()

    @classmethod
    def get_student_score_stats(cls) -> list[dict]:
        with getDatabaseSession() as session:
            results = (
                session.query(
                    StudentScore.student_id,
                    StudentScore.name,
                    StudentScore.student_tag,
                    func.count(StudentScore.id).label("course_count"),
                    func.sum(StudentScore.credit).label("total_credit"),
                    func.sum(StudentScore.credit_gpa).label("total_credit_gpa"),
                    (
                        func.sum(StudentScore.credit_gpa)
                        / func.sum(StudentScore.credit)
                    ).label("avg_gpa"),
                )
                .group_by(
                    StudentScore.student_id, StudentScore.name, StudentScore.student_tag
                )
                .all()
            )

            return [
                {
                    "student_id": r[0],
                    "name": r[1],
                    "student_tag": r[2],
                    "course_count": r[3],
                    "total_credit": float(r[4] or 0),
                    "total_credit_gpa": float(r[5] or 0),
                    "avg_gpa": round(float(r[6] or 0), 2),
                }
                for r in results
            ]
